﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;
using Architecture.DataLayer.DbAccess;
using CommonComponents.Cache;
using CommonComponents.Configuration;
using CommonComponents.ExceptionHandling;
using LASLogic.BusinessLayer.DataModel;
using LASLogic.BusinessLayer.Utilities;

namespace LASLogic.DataLayer.Implementor
{
    public class EntryDataManager
    {
        /// <summary>
        /// Se utiliza para todos los métodos que no son orientados a usuarios
        /// </summary>
        private readonly CacheManager _cacheManager;


        #region Public Methods

        /// <summary>
        /// Metodo que permite insertar un nuevo asiento en la base de datos
        /// </summary>
        /// <param name="entryType">Tipo de asiento a insertar, automatico o manual</param>
        /// <param name="companyId">Identificador único de la compañía</param>
        /// <param name="userId">Identificador único del usuario</param>
        /// <param name="documentDate">Fecha del asiento a contabilizar</param>
        /// <param name="comments">Comentarios adicionales que se hacen sobre el asiento</param>
        /// <param name="reference1">Referencia numero 1</param>
        /// <param name="reference2">Referencia numero 2</param>
        /// <param name="reference3">Referencia numero 3</param>
        /// <returns>Booleano indicando el estado de la insercion</returns>
        public bool InsertEntry(string entryType, int companyId, int userId, SqlDateTime documentDate,
            string comments, string reference1, string reference2, string reference3)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = appConfig.GetProperty(Constants.SP_NAME_INSERT_BOOK_ENTRY);
                var parameters = new SqlParameter[8];

                var comment = string.IsNullOrEmpty(comments) ? DBNull.Value.ToString() : comments;
                var ref1 = string.IsNullOrEmpty(reference1) ? DBNull.Value.ToString() : reference1;
                var ref2 = string.IsNullOrEmpty(reference2) ? DBNull.Value.ToString() : reference2;
                var ref3 = string.IsNullOrEmpty(reference3) ? DBNull.Value.ToString() : reference3;

                parameters[0] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_INSERT_BOOK_ENTRY_ENTRY_TYPE), entryType);
                parameters[1] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_INSERT_BOOK_ENTRY_COMPANY_ID), companyId);
                parameters[2] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_INSERT_BOOK_ENTRY_USER_ID), userId);
                parameters[3] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_INSERT_BOOK_ENTRY_DOCUMENT_DATE), documentDate);
                parameters[4] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_INSERT_BOOK_ENTRY_COMMENTS), comment);
                parameters[5] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_INSERT_BOOK_ENTRY_REFERENCE_1), ref1);
                parameters[6] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_INSERT_BOOK_ENTRY_REFERENCE_2), ref2);
                parameters[7] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_INSERT_BOOK_ENTRY_REFERENCE_3), ref3);
                var dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                return GetResult(dataHelper.ExecuteStoredProcedure(spName, parameters), dataHelper);
            }
            catch (Exception ex)
            {
                ExceptionHandlingManager.ManageException(ex, ExceptionHandlingManager.ExceptionPolicyType.DataLayer);
                return false;
            }
        }

        /// <summary>
        /// Permite borrar un asiento de la base de datos generando un contra asiento al asiento especificado
        /// </summary>
        /// <param name="entryId">Identificador del asiento a borrar</param>
        /// <returns>Estado del borrado del asiento</returns>
        public bool EraseEntry(int entryId)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = appConfig.GetProperty(Constants.SP_NAME_ERASE_ENTRY);
                var parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_ERASE_ENTRY_ENTRY_ID), entryId);
                var dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                return GetResult(dataHelper.ExecuteStoredProcedure(spName, parameters), dataHelper);
            }
            catch (Exception ex)
            {
                ExceptionHandlingManager.ManageException(ex, ExceptionHandlingManager.ExceptionPolicyType.DataLayer);
                return false;
            }
        }


        /// <summary>
        /// Permite asignar una Cuenta a un Asiento
        /// </summary>
        /// <param name="accountId">Identificador de la cuenta a ligar</param>
        /// <param name="transactionType">Tipo de transaccion, si es debe o haber</param>
        /// <param name="accountBalance">LocalBalance de la cuenta</param>
        /// <param name="accountBalanceSystem">LocalBalance de la cuenta en moneda del sistema</param>
        /// <param name="foreignBalance">LocalBalance de la cuenta en moneda extrangera</param>
        /// <param name="exchangeRate">Tipo de cambio de la cuenta</param>
        /// <returns>Permite obtener si la cuenta fue ligada exitosamente</returns>
        public bool AddAccountToEntry(int accountId, string transactionType, SqlMoney accountBalance, SqlMoney accountBalanceSystem, SqlMoney foreignBalance, SqlMoney exchangeRate)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = appConfig.GetProperty(Constants.SP_NAME_ADD_ACCOUNT_TO_ENTRY);
                var parameters = new SqlParameter[6];
                parameters[0] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_ADD_ACCOUNT_TO_ENTRY_ACCOUNT_ID), accountId);
                parameters[1] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_ADD_ACCOUNT_TO_ENTRY_TRANSACTION_TYPE), transactionType);
                parameters[2] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_ADD_ACCOUNT_TO_ENTRY_ACCOUNT_BALANCE), accountBalance);
                parameters[3] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_ADD_ACCOUNT_TO_ENTRY_ACCOUNT_BALANCE_SYSTEM), accountBalanceSystem);
                parameters[4] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_ADD_ACCOUNT_TO_ENTRY_ACCOUNT_BALANCE_FOREIGN), foreignBalance);
                parameters[5] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_ADD_ACCOUNT_TO_ENTRY_ACCOUNT_EXCHANGE_RATE), exchangeRate);
                var dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                return GetResult(dataHelper.ExecuteStoredProcedure(spName, parameters), dataHelper);
            }
            catch (Exception ex)
            {
                ExceptionHandlingManager.ManageException(ex, ExceptionHandlingManager.ExceptionPolicyType.DataLayer);
                return false;
            }
        }


        /// <summary>
        /// Permite obtener todos los asientos de una compañia en particular y acotado por fechas
        /// </summary>
        /// <param name="companyId">Identificador de una compañia</param>
        /// <param name="startDate">Fecha que marca el inicio de donde se quieren traer asientos</param>
        /// <param name="endDate">Fecha que marca el final de donde se quieren traer fechas</param>
        /// <returns>Obtiene todos los asientos de una compañia</returns>
        public List<Entry> GetEntriesFromCompany(int companyId, DateTime startDate, DateTime endDate)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = appConfig.GetProperty(Constants.SP_NAME_GET_ALL_ENTRIES);
                var parameters = new SqlParameter[3];
                parameters[0] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_GET_ALL_ENTRIES_COMPANY_ID), companyId);
                parameters[1] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_GET_ALL_ENTRIES_START_DATE), startDate);
                parameters[2] = new SqlParameter(appConfig.GetProperty(Constants.SP_PARAM_GET_ALL_ENTRIES_END_DATE), endDate);
                var dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                var result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                if (result.HasRows)
                {
                    result.Read();
                    string entriesXml = result.GetSqlString(0).ToString();
                    dataHelper.CloseReader();
                    entriesXml = Constants.ROOT_NODE_BEGINNING + entriesXml + Constants.ROOT_NODE_ENDING;
                    var xDoc = new XmlDocument();
                    xDoc.LoadXml(entriesXml);
                    return BuildEntries(xDoc);
                }
                return new List<Entry>();
            }
            catch (Exception ex)
            {
                ExceptionHandlingManager.ManageException(ex, ExceptionHandlingManager.ExceptionPolicyType.DataLayer);
                return null;
            }
        }

        #endregion


        #region Protected / Private Methods

        /// <summary>
        /// Utiliza un documento de XML para armar una lista de Asientos
        /// El documento XML tiene la estructura proporcionada por el SP de la base
        /// de datos. Para ver la estructura buscar el SP 'GetAllEntriesFromCompany'
        /// </summary>
        /// <param name="xDoc">Documento XML con la información de los asientos</param>
        /// <returns></returns>
        private static List<Entry> BuildEntries(XmlDocument xDoc)
        {
            System.Globalization.NumberFormatInfo ni = null;
            System.Globalization.CultureInfo ci = System.Globalization.CultureInfo.InstalledUICulture;
            ni = (System.Globalization.NumberFormatInfo)ci.NumberFormat.Clone();
            ni.NumberDecimalSeparator = ".";

            AppConfigReader appConfig = AppConfigReader.GetInstance();
            XmlNodeList entryList = xDoc.GetElementsByTagName(appConfig.GetProperty(Constants.NODE_NAME_ENTRY));
            var entries = new List<Entry>();
            for (int entryIndex = 0; entryIndex < entryList.Count; entryIndex++)
            {
                XmlNode entryNode = entryList[entryIndex];
                int periodId = Convert.ToInt32(entryNode.Attributes.GetNamedItem(appConfig.GetProperty(Constants.TAG_NAME_GET_ALL_ENTRIES_PERIOD_ID)).Value);
                string monthStatus = entryNode.Attributes.GetNamedItem(appConfig.GetProperty(Constants.TAG_NAME_GET_ALL_ENTRIES_MONTH_STATUS)).Value;
                int accountingMonthId = Convert.ToInt32(entryNode.Attributes.GetNamedItem(appConfig.GetProperty(Constants.TAG_NAME_GET_ALL_ENTRIES_MONTH_ID)).Value);
                int companyId = Convert.ToInt32(entryNode.Attributes.GetNamedItem(appConfig.GetProperty(Constants.TAG_NAME_GET_ALL_ENTRIES_COMPANY_ID)).Value);
                int entryId = Convert.ToInt32(entryNode.Attributes.GetNamedItem(appConfig.GetProperty(Constants.TAG_NAME_GET_ALL_ENTRIES_ENTRY_ID)).Value);
                int entryUniqueId = Convert.ToInt32(entryNode.Attributes.GetNamedItem(appConfig.GetProperty(Constants.TAG_NAME_GET_ALL_ENTRIES_ENTRY_UNIQUE_ID)).Value);
                int userId = Convert.ToInt32(entryNode.Attributes.GetNamedItem(appConfig.GetProperty(Constants.TAG_NAME_GET_ALL_ENTRIES_USER_ID)).Value);
                string userName = entryNode.Attributes.GetNamedItem(appConfig.GetProperty(Constants.TAG_NAME_GET_ALL_ENTRIES_PERIOD_ID)).Value;
                DateTime documentDate = Convert.ToDateTime(entryNode.Attributes.GetNamedItem(appConfig.GetProperty(Constants.TAG_NAME_GET_ALL_ENTRIES_DOCUMENT_DATE)).Value);
                DateTime accountedDate = Convert.ToDateTime(entryNode.Attributes.GetNamedItem(appConfig.GetProperty(Constants.TAG_NAME_GET_ALL_ENTRIES_ACCOUNTED_DATE)).Value);
                string entryType = entryNode.Attributes.GetNamedItem(appConfig.GetProperty(Constants.TAG_NAME_GET_ALL_ENTRIES_ENTRY_TYPE)).Value;
                string comment = entryNode.Attributes.GetNamedItem(appConfig.GetProperty(Constants.TAG_NAME_GET_ALL_ENTRIES_COMMENTS)).Value;
                string reference1 = entryNode.Attributes.GetNamedItem(appConfig.GetProperty(Constants.TAG_NAME_GET_ALL_ENTRIES_REFERENCE1)).Value;
                string reference2 = entryNode.Attributes.GetNamedItem(appConfig.GetProperty(Constants.TAG_NAME_GET_ALL_ENTRIES_REFERENCE2)).Value;
                string reference3 = entryNode.Attributes.GetNamedItem(appConfig.GetProperty(Constants.TAG_NAME_GET_ALL_ENTRIES_REFERENCE3)).Value;
                var entry = new Entry(entryId, entryUniqueId, periodId, accountingMonthId, monthStatus, companyId, entryType, userId, userName,
                                      documentDate, accountedDate, null, null,
                                      comment, reference1, reference2, reference3);

                XmlNodeList transactionList = entryNode.ChildNodes;

                var hasAccountsCredit = new List<EntryHasAccount>();
                var hasAccountsDebit = new List<EntryHasAccount>();
                for (int transactionIndex = 0; transactionIndex < transactionList.Count; transactionIndex++)
                {
                    XmlNode transactionNode = transactionList[transactionIndex];

                    string transactionType = transactionNode.Attributes.GetNamedItem(appConfig.GetProperty(Constants.TAG_NAME_GET_ALL_ENTRIES_TRANSACTION_TYPE)).Value;
                    XmlNodeList accountList = transactionNode.ChildNodes;

                    for (int accountIndex = 0; accountIndex < accountList.Count; accountIndex++)
                    {

                        XmlNode accountNode = accountList[accountIndex];
                        int accountId = Convert.ToInt32(accountNode.Attributes.GetNamedItem(appConfig.GetProperty(Constants.TAG_NAME_GET_ALL_ENTRIES_ACCOUNT_ID)).Value);
                        decimal systemBalance = Convert.ToDecimal(float.Parse(accountNode.Attributes.GetNamedItem(appConfig.GetProperty(Constants.TAG_NAME_GET_ALL_ENTRIES_SYSTEM_BALANCE)).Value.Replace(',','.'),ni));
                        decimal localBalance = Convert.ToDecimal(float.Parse(accountNode.Attributes.GetNamedItem(appConfig.GetProperty(Constants.TAG_NAME_GET_ALL_ENTRIES_BALANCE)).Value.Replace(',', '.'), ni));
                        decimal foreignBalance = Convert.ToDecimal(float.Parse(accountNode.Attributes.GetNamedItem(appConfig.GetProperty(Constants.TAG_NAME_GET_ALL_ENTRIES_FOREIGN_BALANCE)).Value.Replace(',', '.'), ni));
                        decimal exchangeRate = Convert.ToDecimal(float.Parse(accountNode.Attributes.GetNamedItem(appConfig.GetProperty(Constants.TAG_NAME_GET_ALL_ENTRIES_EXCHANGE_RATE)).Value.Replace(',', '.'), ni));
                        var entryHasAccount = new EntryHasAccount(accountId, localBalance, systemBalance, foreignBalance, exchangeRate, transactionType);
                        if (transactionType == appConfig.GetProperty(Constants.DEBIT_NAME))
                        {
                            hasAccountsDebit.Add(entryHasAccount);
                        }
                        else
                        {
                            hasAccountsCredit.Add(entryHasAccount);
                        }
                    }
                }
                entry.CreditAccounts = hasAccountsCredit;
                entry.DebitAccounts = hasAccountsDebit;
                entries.Add(entry);
            }
            return entries;
        }


        /// <summary>
        /// Dice si el Stored Procedure fue efectuado correctamente o no
        /// </summary>
        /// <param name="result">ResultSet que indica si fue efectuado correctamente o no</param>
        /// <param name="helper">DataHelper con que la operación fue efectuada</param>
        /// <returns>Verdadero si el SP se ejecutó bien, de lo contrario falso</returns>
        private static bool GetResult(SqlDataReader result, SqlDataHelper helper)
        {
            result.Read();
            bool actionPerformed = Convert.ToInt32(result[AppConfigReader.GetInstance().GetProperty(Constants.SP_OUTPUT_ACTION_PERFORMED_RESULT)]) == 0 ? false : true;
            helper.CloseConnection();
            return actionPerformed;
        }
        #endregion

        #region Singleton

        protected EntryDataManager()
        {
            _cacheManager = new CacheManager();
        }

        /// <summary>
        /// Única instancia de esta clase
        /// </summary>
        private static EntryDataManager _instance;

        /// <summary>
        /// Método para implementar el patrón de Singleton
        /// </summary>
        /// <returns>Instancia única de la clase</returns>
        public static EntryDataManager GetInstance()
        {
            if (_instance == null)
                _instance = new EntryDataManager();
            return _instance;
        }

        #endregion
    }
}